import java.beans.Encoder;
import java.io.File;
import java.net.URLEncoder;
import java.sql.*;
import java.util.List;
import java.util.ArrayList;
import java.util.Map;
import java.util.HashMap;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import org.w3c.dom.*;

/**
 * @author Wei Chen, Yu Qiao, Igor Tolkachev
 * @version 1.01
 * Parse xml-based Twitter data into Java objects and map them into H2 relational database.
 */
public final class TwitterDataParser {
	//connection to test db with sa as the user and no pw
	private static Connection conn;
	private List<Tweet> tweet_lst=new ArrayList<Tweet>();
	private String xmlUrl;
	//List<Tweet> find_result_lst=new ArrayList<Tweet>();
	
	public TwitterDataParser() throws Exception{
		Class.forName("org.h2.Driver");
		conn= DriverManager.getConnection("jdbc:h2:tcp://localhost/~/twitterdb", "sa", "");
	}
	public boolean parseXML(String xmlUrl){
		this.xmlUrl=xmlUrl;
		//xmlUrl example:lab1/MyFile.xml
		try{
			File file = new File(xmlUrl);
			DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
			DocumentBuilder db = dbf.newDocumentBuilder();
			Document document = db.parse(file);
			System.out.print("Data found!");
			document.getDocumentElement().normalize();
			System.out.println("Root element "+ document.getDocumentElement().getNodeName());
			NodeList tweet_nodes = document.getElementsByTagName("tweet");
			System.out.println("Information of the tweets:");
			for (int i = 0; i < tweet_nodes.getLength(); i++) {
				Tweet t=new Tweet();
				Node curTweet = tweet_nodes.item(i);
				NamedNodeMap tweetAttributes = curTweet.getAttributes();
				
				for(int j=0;j<tweetAttributes.getLength();j++){
					Node curAtt = tweetAttributes.item(j);
					if(curAtt.getNodeName()=="id"){
						t.set_id(curAtt.getNodeValue());
					}else if(curAtt.getNodeName()=="created_at"){
						t.set_created_at(curAtt.getNodeValue());
					}
					System.out.println(curAtt.getNodeName() + "=" + curAtt.getNodeValue());
				}
				
				NodeList element_nodes = curTweet.getChildNodes();
				for(int j=0; j<element_nodes.getLength();j++){
					Node curNode=element_nodes.item(j);
					if (curNode.getNodeType() == Node.ELEMENT_NODE) {
						switch(curNode.getNodeName()){
							case "from_user_id":
								t.set_from_user_id(curNode.getTextContent());
								break;
							case "from_user":
								t.set_from_user(curNode.getTextContent());
								break;
							case "to_user_id":
								t.set_to_user_id(curNode.getTextContent());
								break;
							case "iso_language_code":
								t.set_iso_language_code(curNode.getTextContent());
								break;
							case "result_type":
								t.set_result_type(curNode.getTextContent());
								break;
							case "profile_image_url":
								t.set_profile_image_url(curNode.getTextContent());
								break;
							case "source":
								t.set_source(curNode.getTextContent());
								break;
							case "text":
								t.set_text(curNode.getTextContent());
								break;
							case "geo_id":
								t.set_geo_id(curNode.getTextContent());
								break;
						}
					}
				}//end for
				tweet_lst.add(t);
			}//end for
			return true;
		}catch (Exception e) {
			// TODO: handle exception
			e.setStackTrace(null);
			return false;
		}
	}

	@SuppressWarnings("deprecation")
	public boolean updateDB(){
		try{
			//update H2 db using tweet_lst
			Statement stmt=conn.createStatement();
			stmt.execute("DELETE FROM tweet");
			String query="";
			if(tweet_lst.size()>0){
				query="INSERT INTO tweet VALUES (\'"
						+tweet_lst.get(0).get_id()+"\',\'"
						+tweet_lst.get(0).get_created_at()+"\',\'"
						+tweet_lst.get(0).get_from_user_id()+"\',\'"
						+URLEncoder.encode(tweet_lst.get(0).get_from_user())+"\',\'"
						+tweet_lst.get(0).get_to_user_id()+"\',\'"
						+URLEncoder.encode(tweet_lst.get(0).get_profile_image_url())+"\',\'"
						+tweet_lst.get(0).get_iso_language_code()+"\',\'"
						+tweet_lst.get(0).get_result_type()+"\',\'"
						+URLEncoder.encode(tweet_lst.get(0).get_source())+"\',\'"
						+URLEncoder.encode(tweet_lst.get(0).get_text())+"\',"
						+tweet_lst.get(0).get_geo_id()+")";
				for(int i=1;i<tweet_lst.size();i++){
					query+=",(\'"
							+tweet_lst.get(i).get_id()+"\',\'"
							+tweet_lst.get(i).get_created_at()+"\',\'"
							+tweet_lst.get(i).get_from_user_id()+"\',\'"
							+URLEncoder.encode(tweet_lst.get(i).get_from_user())+"\',\'"
							+tweet_lst.get(i).get_to_user_id()+"\',\'"
							+URLEncoder.encode(tweet_lst.get(i).get_profile_image_url())+"\',\'"
							+tweet_lst.get(i).get_iso_language_code()+"\',\'"
							+tweet_lst.get(i).get_result_type()+"\',\'"
							+URLEncoder.encode(tweet_lst.get(i).get_source())+"\',\'"
							+URLEncoder.encode(tweet_lst.get(i).get_text())+"\',"
							+tweet_lst.get(i).get_geo_id()+")";
				}
				stmt.execute(query);
			}
			return true;
		}catch (Exception e) {
			// TODO: handle exception
			System.out.println(e.getMessage());
			return false;
		}
	}

	//"keyword"->"Java;C;VB", "location"->"Columbus, OH; Dayton, OH"
	public static boolean find(Map<String, String> params){
		try{
			return true;
		}catch (Exception e) {
			// TODO: handle exception
			return false;
		}
	}
	
	public static void main(String[] args) throws Exception{
		TwitterDataParser myParser=new TwitterDataParser();
		myParser.parseXML("data/Tweet.xml");
		myParser.updateDB();
	}
}


